import sqlite3
import time

import ajc

db_one = sqlite3.connect('sep.one.sqlite3')
cu_one = db_one.cursor()
cu_one.execute("CREATE TABLE IF NOT EXISTS my_table (fid INTEGER, key TEXT, value TEXT, PRIMARY KEY (fid, key))")
db_two = sqlite3.connect('sep.two.sqlite3')
cu_two = db_two.cursor()
cu_two.execute("CREATE TABLE IF NOT EXISTS my_table (fid INTEGER, key TEXT, type TEXT, value TEXT, PRIMARY KEY (fid, key))")

N = 100000

def i1():
    ajc.util.time_start(msg = '1 column insert')
    for i in range(N): 
        cu_one.execute('INSERT OR REPLACE INTO my_table (fid, key, value) VALUES (?,?,?)', (i, 'a_key', 'i' + str(i),))     
        cu_one.execute('INSERT OR REPLACE INTO my_table (fid, key, value) VALUES (?,?,?)', (i, 'b_key', 'i' + str(4),))     
    db_one.commit()
    ajc.util.time_stop()

def i2():
    ajc.util.time_start(msg = '2 column insert')
    for i in range(N): 
        cu_two.execute('INSERT OR REPLACE INTO my_table (fid, key, type, value) VALUES (?,?,?,?)', (i, 'a_key', 'i', i,))     
        cu_two.execute('INSERT OR REPLACE INTO my_table (fid, key, type, value) VALUES (?,?,?,?)', (i, 'b_key', 'i', 4,))     
    db_two.commit()
    ajc.util.time_stop()

def e1():
    ajc.util.time_start(msg = '1 column query equals')
    for row in cu_one.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND value = 'i4'"):
        fid = row[0]
    db_one.commit()
    ajc.util.time_stop()

def e2():
    ajc.util.time_start(msg = '2 column query equals')
    for row in cu_two.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND type = 'i' AND value = '4'"):
        fid = row[0]
    db_two.commit()
    ajc.util.time_stop()

def l1():
    ajc.util.time_start(msg = '1 column query less than')
    for row in cu_one.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND substr(value,1,1) = 'i' AND 1*substr(value,2) < 75000"):
        fid = row[0]
        #print "A", fid
    db_one.commit()
    ajc.util.time_stop()

def l2():
    ajc.util.time_start(msg = '2 column query less than')
    for row in cu_two.execute("SELECT fid FROM my_table WHERE key = 'b_key' AND type = 'i' AND 1*value < 75000"):
        fid = row[0]
        #print "B", fid
    db_two.commit()
    ajc.util.time_stop()

i1()
i2()
i2()
i2()
i1()
i1()
i2()
i1()

e2()
e1()
e2()
e2()
e1()
e1()
e2()
e1()

l2()
l1()
l2()
l2()
l1()
l1()
l2()
l1()



